CREATE PROCEDURE [usp_GetCustomer] 
@startRowIndex int,
@us_id int,
@maximumRows int, 
@totalRows int OUTPUT
AS

DECLARE @first_id int, @startRow int

SET @startRowIndex =  (@startRowIndex - 1) * @maximumRows

IF @startRowIndex = 0 
SET @startRowIndex = 1

SET ROWCOUNT @startRowIndex

SELECT @first_id = Cus_Id 
FROM Customers 
WHERE Us_Id = @us_id 
ORDER BY FirstName

PRINT @first_id

SET ROWCOUNT @maximumRows

SELECT * FROM Customers WHERE 
Cus_Id >= @first_id
AND Us_Id = @us_id
ORDER BY FirstName
 
SET ROWCOUNT 0

-- GEt the total rows 
SELECT @totalRows = COUNT(Cus_Id) FROM Customers WHERE Us_Id = @us_id
GO
